using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;


using Material.Object;
using Material.DAL;
namespace Material.Entity
{
	public class TblTransPenjBarangHDREntity
	{
		private Accessor accessor;
		private string connString;

		public TblTransPenjBarangHDREntity (string _connString)
		{
			 this.connString = _connString;
		}

		public int Insert(TblTransPenjBarangHDR tbltranspenjbaranghdr)
		{
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("INSERT INTO [TblTransPenjBarangHDR]");
            sql.AppendLine("(");
            sql.AppendLine("[TglJual]");
            sql.AppendLine(",[CreatedDate]");
            sql.AppendLine(",[CreatedBy]");
            sql.AppendLine(")");
            sql.AppendLine("VALUES (");
            sql.AppendLine("@TglJual");
            sql.AppendLine(",@CreatedDate");
            sql.AppendLine(",@CreatedBy");
            sql.AppendLine(")");

			 int result = -1;
			 accessor = new Accessor(true, connString);
			 accessor.CommandText = sql.ToString();
			 accessor.CommandType = CommandType.Text;
			 accessor.Add("@TglJual" ,tbltranspenjbaranghdr.TglJual );
			 accessor.Add("@CreatedDate" ,tbltranspenjbaranghdr.CreatedDate );
			 accessor.Add("@CreatedBy" ,tbltranspenjbaranghdr.CreatedBy );

			 result = Excutor.ExecuteNonQuery(accessor);
			 if (result != -1)
				 accessor.CommitTransaction();
			 else
				 accessor.RollBackTransaction();
			 return result;
		}

		public int Update(TblTransPenjBarangHDR tbltranspenjbaranghdr)
		{
			 int result = -1;
			 accessor = new Accessor(true, connString);
			 accessor.CommandText = "TblTransPenjBarangHDR_Update";
			 accessor.CommandType = CommandType.StoredProcedure;
			 //accessor.Add(@"IdTransHDR" ,tbltranspenjbaranghdr.IdTransHDR );
			 accessor.Add(@"TglJual" ,tbltranspenjbaranghdr.TglJual );
			 //accessor.Add(@"CreatedDate" ,tbltranspenjbaranghdr.CreatedDate );
			// accessor.Add(@"CreatedBy" ,tbltranspenjbaranghdr.CreatedBy );
			 accessor.Add(@"ModifiedDate" ,tbltranspenjbaranghdr.ModifiedDate );
			 accessor.Add(@"ModifiedBy" ,tbltranspenjbaranghdr.ModifiedBy );

			 result = Excutor.ExecuteNonQuery(accessor);
			 if (result != -1)
				 accessor.CommitTransaction();
			 else
				 accessor.RollBackTransaction();
			 return result;
		}

		public int Delete(int IdHDR)
		{
			

             StringBuilder sql = new StringBuilder();
             sql.AppendLine("DELETE FROM TblTransPenjBarangHDR WHERE IdHDR=@IdHDR");
            

             int result = -1;
             accessor = new Accessor(true, connString);
             accessor.CommandText = sql.ToString();
             accessor.CommandType = CommandType.Text;
             accessor.Add("@IdHDR", IdHDR);

			 result = Excutor.ExecuteNonQuery(accessor);
			 if (result != -1)
				 accessor.CommitTransaction();
			 else
				 accessor.RollBackTransaction();
			 return result;
		}

		public TblTransPenjBarangHDR GetDataByID(TblTransPenjBarangHDR tbltranspenjbaranghdr)
		{
			 TblTransPenjBarangHDR result = new TblTransPenjBarangHDR();
			 accessor = new Accessor(true, connString);
			 accessor.CommandText = "TblTransPenjBarangHDR_SelectByID";
			 accessor.CommandType = CommandType.StoredProcedure;
			// accessor.Add(@"IdTransHDR" ,tbltranspenjbaranghdr.IdTransHDR);

			 SqlDataReader rdr = Excutor.GetDataReader(accessor);
			 if (rdr.HasRows)
			 {
				 while (rdr.Read())
					 {
                         //result.IdTransHDR = rdr.GetString(0);
						 result.TglJual = rdr.GetDateTime(1);
						 result.CreatedDate = rdr.GetDateTime(2);
						 result.CreatedBy = rdr.GetString(3);
						 result.ModifiedDate = rdr.GetDateTime(4);
						 result.ModifiedBy = rdr.GetString(5);
					  }
			}
			 return result;
		}

        public int GetDataMaxID()
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT MAX(IdHDR) FROM TblTransPenjBarangHDR");

            int result = 0;
            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;
          
            object Id = Excutor.ExecuteScalar(accessor);
            if (Id != DBNull.Value)
                result = Convert.ToInt32(Id);// + 1;
            
            return result;
        }

		public List<TblTransPenjBarangHDR> GetDataAll()
		{
			 List<TblTransPenjBarangHDR> result = new List<TblTransPenjBarangHDR>();
			 accessor = new Accessor(true, connString);
			 accessor.CommandText = "TblTransPenjBarangHDR_SelectAll";
			 accessor.CommandType = CommandType.StoredProcedure;

			 SqlDataReader rdr = Excutor.GetDataReader(accessor);
			 if (rdr.HasRows)
			 {
				 while (rdr.Read())
					 {
						 TblTransPenjBarangHDR obj = new TblTransPenjBarangHDR();
                         //obj.IdTransHDR = rdr.GetString(0);
						 obj.TglJual = rdr.GetDateTime(1);
						 obj.CreatedDate = rdr.GetDateTime(2);
						 obj.CreatedBy = rdr.GetString(3);
						 obj.ModifiedDate = rdr.GetDateTime(4);
						 obj.ModifiedBy = rdr.GetString(5);
						 result.Add(obj);
					  }
			}
			 return result;
		}

        //public TblTransPenjBarangHDR GetDataByMaxID()
        //{
        //    TblTransPenjBarangHDR result = new TblTransPenjBarangHDR();
        //    accessor = new Accessor(true, connString);
        //    accessor.CommandText = "TblTransPenjBarangHDR_GetMaxID";
        //    accessor.CommandType = CommandType.StoredProcedure;


        //    SqlDataReader rdr = Excutor.GetDataReader(accessor);
        //    if (rdr.HasRows)
        //    {
        //        while (rdr.Read())
        //        {
        //            int no = rdr.GetInt32(0) + 1;
        //            int tahun = rdr.GetInt32(1);
        //            if(tahun.ToString()!=DateTime.Now.ToString("yyyy"))
        //                result.IdTransHDR = @"PNJ\" + DateTime.Now.ToString("yyyy") + @"\" + DateTime.Now.ToString("MM") + @"\0000001";
        //            else
        //                result.IdTransHDR = @"PNJ\" + DateTime.Now.ToString("yyyy") + @"\" + DateTime.Now.ToString("MM") + @"\" + no.ToString().PadLeft(7, '0');
        //        }
        //    }
        //    else
        //    {
        //        result.IdTransHDR = @"PNJ\" + DateTime.Now.ToString("yyyy") + @"\" + DateTime.Now.ToString("MM") + @"\0000001";;

        //    }
        //    return result;
        //}

        public DataSet GetFindDataAll()
        {
            StringBuilder s = new StringBuilder();
            s.AppendLine(" SELECT");
            s.AppendLine("A.IdHDR,");
            s.AppendLine("CONVERT(VARCHAR(20),A.TglJual,113) AS TglJual");
            s.AppendLine("FROM TblTransPenjBarangHDR A");
            s.AppendLine("ORDER BY A.TglJual DESC");

            DataSet result = new DataSet();
            accessor = new Accessor(true, connString);
            accessor.CommandText = s.ToString();// "TblTransPemBarangDTL_SelectFindAll";
            accessor.CommandType = CommandType.Text;

            result = Excutor.GetDataSet(accessor);

            return result;
        }

        public DataSet GetFindDataAll(string parameter, string value)
        {
            string  [] param = value.Split('|');
            StringBuilder s = new StringBuilder();
            s.AppendLine(" SELECT");
            s.AppendLine("A.IdHDR,");
            s.AppendLine("CONVERT(VARCHAR(20),A.TglJual,113) AS TglJual");
            s.AppendLine("FROM TblTransPenjBarangHDR A");
            
            s.AppendLine("WHERE CONVERT(varchar,A.TglJual,103)>='" + param[0].ToString() + "'  AND  CONVERT(varchar,A.TglJual,103)<= '" + param[1].ToString() + "'");
            s.AppendLine("ORDER BY A.TglJual DESC");

            DataSet result = new DataSet();
            accessor = new Accessor(true, connString);
            accessor.CommandText = s.ToString();
            accessor.CommandType = CommandType.Text;
            result = Excutor.GetDataSet(accessor);

            return result;
        }


	}
}
